﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using XuanFe.Database.Model;

namespace XuanFe.Database.DAL
{
    public class OnlineBookingDAL
    {
        Nullable<DateTime> NullDateTime = null;

        private static OnlineBookingDAL instance = new OnlineBookingDAL();


        private static readonly string SQL_SELECT_ALL = "select * from ws_online_booking where 1=1";

        private static readonly string SQL_SELECT_PHONE = " and customer_phone=@customerPhone";
        private static readonly string SQL_SELECT_NAME = " and customer_name=@customerName";
        private static readonly string SQL_SELECT_HAND = " and handle=@handle";
        private static readonly string SQL_SELECT_DATE = " and create_date between @StartTime and @EndTime";
        private static readonly string SQL_SELECT_LIKE_PHONE = " and customer_phone like @customerPhone";
        private static readonly string SQL_SELECT_LIKE_NAME = " and customer_name like @customerName";
        private const string SQL_ORDER_BY_TIME = " order by create_date desc";

        private const string SQL_UPDATE_WS_BOOKING = "update ws_online_booking set customer_name=@CustomerName,customer_phone=@CustomerPhone,handle=@handle,remark=@Remark where 1=1";
        private const string SQL_WHERE_BOOKING_ID = " and id=@BookingId ";

        private const string PARM_BOOKING_ID = "@BookingId";
        private const string PARM_HANDLE = "@handle";
        private const string PARM_CUSTOMER_NAME = "@CustomerName";
        private const string PARM_CUSTOMER_PHONE = "@CustomerPhone";
        private const string PARM_CUSTOMER_REMARK = "@Remark";

        public static OnlineBookingDAL Instance
        {
            get
            {
                return instance;
            }
        }

        public DataSet GetDataSet(QueryOnlineBookingModel model)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_ALL);
            List<MySqlParameter> parms = new List<MySqlParameter>();
            if (!String.IsNullOrEmpty(model.Id))
            {
                sbSQL.Append(SQL_WHERE_BOOKING_ID);
                parms.Add(new MySqlParameter(PARM_BOOKING_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value =model.Id });
            }

            if (model.isCustomerName)
            {
                sbSQL.Append(SQL_SELECT_LIKE_NAME);
                parms.Add(new MySqlParameter("@customerName", MySqlDbType.String) { Direction = ParameterDirection.Input, Value = string.Format("%{0}%", model.customerName) });
            }
            else if (!string.IsNullOrEmpty(model.customerName))
            {
                sbSQL.Append(SQL_SELECT_NAME);
                parms.Add(new MySqlParameter("@customerName", MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.customerName });

            }

            if (model.isCustomerPhone)
            {
                sbSQL.Append(SQL_SELECT_LIKE_PHONE);
                parms.Add(new MySqlParameter("@customerPhone", MySqlDbType.String) { Direction = ParameterDirection.Input, Value = string.Format("%{0}%", model.customerPhone) });
            }
            else if (!string.IsNullOrEmpty(model.customerPhone))
            {
                sbSQL.Append(SQL_SELECT_PHONE);
                parms.Add(new MySqlParameter("@customerPhone", MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.customerPhone });
            }
            if (!model.isAll)
            {
                if (model.handle)
                {
                    sbSQL.Append(SQL_SELECT_HAND);
                    parms.Add(new MySqlParameter("@handle", MySqlDbType.UByte) { Direction = ParameterDirection.Input, Value = model.handle });
                }
            }
            if (model.isTime)
            {
                sbSQL.Append(SQL_SELECT_DATE);
                parms.Add(new MySqlParameter("@StartTime", MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.createDateStart });
                parms.Add(new MySqlParameter("@EndTime", MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.createDateEnd });
            }
            sbSQL.Append(SQL_ORDER_BY_TIME);

            return MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, sbSQL.ToString(), parms.ToArray());

        }

        public OnlineBookingModel Get(QueryOnlineBookingModel model)
        {
            using (DataSet ds = this.GetDataSet(model))
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    DataRow t = ds.Tables[0].Rows[0];
                    return new OnlineBookingModel()
                    {
                        Id = Convert.ToString(t.ItemArray[0]),
                        CustomerName = t.IsNull(1) ? string.Empty : Convert.ToString(t.ItemArray[1]),
                        CustomerPhone = t.IsNull(2) ? string.Empty : Convert.ToString(t.ItemArray[2]),
                        EMail = t.IsNull(3) ? string.Empty : Convert.ToString(t.ItemArray[3]),
                        QqNumber = t.IsNull(4) ? string.Empty : Convert.ToString(t.ItemArray[4]),
                        WeChat = t.IsNull(5) ? string.Empty : Convert.ToString(t.ItemArray[5]),
                        TravelType = Convert.ToInt32(t.ItemArray[6]),
                        DepartureCityName = t.IsNull(7) ? string.Empty : Convert.ToString(t.ItemArray[7]),
                        ArrivalCityName = t.IsNull(8) ? string.Empty : Convert.ToString(t.ItemArray[8]),
                        DepartureDate = t.IsNull(9) ? NullDateTime : Convert.ToDateTime(t.ItemArray[9]),
                        ReturnDate = t.IsNull(10) ? NullDateTime : Convert.ToDateTime(t.ItemArray[10]),
                        IdNumber = t.IsNull(11) ? string.Empty : Convert.ToString(t.ItemArray[11]),
                        Handle = Convert.ToInt32(t.ItemArray[13]),
                        Remark = t.IsNull(12) ? string.Empty : Convert.ToString(t.ItemArray[12]),
                        CreatedDate = Convert.ToDateTime(t.ItemArray[14]),
                    };

                }
                return null;
            }
        }

        public int Update(DbTransaction trans, OnlineBookingModel model)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_UPDATE_WS_BOOKING);
            sbSQL.Append(SQL_WHERE_BOOKING_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_BOOKING_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Id });
            parms.Add(new MySqlParameter(PARM_HANDLE, MySqlDbType.Int32) { Direction = ParameterDirection.Input, Value = model.Handle });
            parms.Add(new MySqlParameter(PARM_CUSTOMER_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.CustomerName });
            parms.Add(new MySqlParameter(PARM_CUSTOMER_PHONE, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.CustomerPhone });
            parms.Add(new MySqlParameter(PARM_CUSTOMER_REMARK, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Remark });


            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
    }
}
